create database Student
on
(
name='Student',
filename='D:\sql.mdl',
size=5mb,
maxsize=50mb,
filegrowth=10mb
)
log on
(
name='Student_log',
filename='D:\sql.ldf',
size=5mb,
maxsize=50mb,
filegrowth=10mb
)
go
use Student
go

create table stuInfo
(
stuID int primary key identity(1,1) not null,
stuName varchar(20) not null,
stuAge int not null,
stuSex char(2) not null,
time datetime 
)

create table courseInfo
(
courseID int primary key identity(1,1) not null,
courseName varchar(20) not null,
courseMarks int not null
)

create table scoreInfo
(
scoreID int primary key identity(1,1) not null,
stuID int not null,
courseID int not null,
score int not null,
)

insert into stuInfo(stuName,stuAge,stuSex) values ('Tom','19','1'),('Jack','20','0'),
('Rose','21','1'),('Lulu','19','1'),('Lili','21','0')
insert into stuInfo values('abc','20','1','2007-01-07 01:11:36.590')

insert into courseInfo values ('JavaBase','4'),('HTML','2'),('JavaScript','2'),('SqlBase','2')

insert into scoreInfo values ('1','1','80'),('1','2','85'),('1','4','50'),('2','1','75'),('2','3','45'),('2','4','75'),
('3','1','45'),('4','1','95'),('4','2','75'),('4','3','90'),('4','4','45')


--1.查询出每个学生所选修的课程的数量和所选修的课程的考试的平均分

select stuID 学生,count(courseID) 数量,AVG(score) 平均分 from scoreInfo group by stuID

--2.查询出每门课程的选修的学生的个数和学生考试的总分

select courseName 课程,count(stuID) 学生个数,sum(score) 总分 from scoreInfo inner join courseInfo 
on scoreInfo.courseID=courseInfo.courseID group by courseInfo.courseID,courseName

--3.查询出性别一样并且年龄一样的学生的信息

select * from stuInfo where stuAge in (select stuAge from stuInfo 
group by stuAge,stuSex having count(stuAge)>1)--法1

select A.* from stuInfo A,stuInfo B where A.stuAge=B.stuAge and A.stuSex=B.stuSex and A.stuID!=b.stuID--法2

--4.查询出学分一样的课程信息

select * from courseInfo where courseMarks in (select courseMarks from courseInfo 
group by courseMarks having count(courseMarks)>1)

--5.查询出参加了考试的学生的学号，姓名，课程号和分数

select scoreInfo.stuID 学号,stuName 姓名,courseID 课程,score 分数 
from scoreInfo inner join stuInfo on scoreInfo.stuID=stuInfo.stuID

--6.查询出参加了考试的学生的学号，课程号，课程名，课程学分和分数

select stuID 学号,courseInfo.courseID 课程号,courseName 课程名,courseMarks 学分,score 分数 
from courseInfo inner join scoreInfo on courseInfo.courseID=scoreInfo.courseID

--7.查询出没有参加考试的学生的学号和姓名
select stuID,stuName from stuInfo
EXCEPT
select stuInfo.stuID,stuName  from scoreInfo 
inner join stuInfo on scoreInfo.stuID=stuInfo.stuID

--8.查询出是周六周天来报到的学生
select stuname,time from stuInfo where DATEPART(dw,time)=1 or DATEPART(dw,time)=7

select * from stuInfo
select * from courseInfo
select * from scoreInfo

--9.查询出姓名中有字母a的学生的信息

select * from stuInfo where stuName like '%a%'

--10.查询出选修了2门课程以上的并且考试平均分在70以上的学生的学号和考试平均分以及选修课程的数量

select stuID,avg(score) 平均分,count(courseID) 选修课程数量 from  scoreInfo group by stuID 
having avg(score) >70 and count(courseID)>2



--首先创建网吧计费系统的数据库，表结构和数据如2.bmp所示，表的说明如下：
create database wbfee
on
(
	name='wbfee',
	filename='D:\wbfee.mdf',
	size=10Mb,
	maxsize=50Mb,
	filegrowth=10%
)
log on
(
	name='wbfee_log',
	filename='D:\wbfee_log.ldf',
	size=10Mb,
	maxsize=50Mb,
	filegrowth=10%
)
go

use wbfee
go
create table tbl_card
(
	id nvarchar(10) primary key,
	passWord nvarchar(15),
	balance money ,
	userName nvarchar(10)
)

create table tbl_computer
(
	id nvarchar(10) primary key,
	onUse int check(onUse in (1,0)),
	note text
)

create table tbl_record
(
	id nvarchar(10) primary key,
	cardId nvarchar(10) foreign key references tbl_card(id),
	ComputerId nvarchar(10) foreign key references tbl_computer(id),
	beginTime datetime,
	endTime datetime,
	fee money
)


insert into tbl_card values
('0023_ABC','555',98,'张军'),
('0025_bbd','abe',300,'朱俊'),
('0036_CCD','',100,'何柳'),
('0045_YGR','0045_YGR',58,'验证'),
('0078_RJV','55885tg',600,'校庆'),
('0089_EDE','zhang',134,'张峻')



insert into tbl_computer values
('02',0,'25555'),
('03',1,'55555'),
('04',0,'66666'),
('05',1,'88888'),
('06',0,'688878'),
('B01',0,'558558')

insert into tbl_record values
(23,'0078_RJV','B01','2007-07-15 19:00:00','2007-07-15 21:00:00','20'),
(34,'0025_bbd','02','2006-12-25 18:00:00','2006-12-25 22:00:00','23'),
(45,'0023_ABC','03','2006-12-23 15:26:00','2006-12-23 22:55:00','50'),
(46,'0023_ABC','03','2006-12-22 15:26:00','2006-12-22 22:55:00','6'),
(47,'0023_ABC','03','2006-12-23 15:26:00','2006-12-23 22:55:00','50'),
(48,'0023_ABC','03','2007-01-06 15:26:00','2007-01-06 22:55:00','6'),
(55,'0023_ABC','03','2006-07-21 15:26:00','2006-07-21 22:55:00','50'),
(64,'0045_YGR','04','2006-12-24 18:00:00','2006-12-24 22:00:00','300'),
(65,'0025_bbd','02','2006-12-28 18:00:00','2006-12-28 22:00:00','23'),
(98,'0025_bbd','02','2006-12-26 18:00:00','2006-12-26 22:00:00','23')


--tbl_record,tbl_computer ,tbl_card
--1. 查询出用户名为'张军'的上网卡的上网记录，要求显示卡号，用户名，机器编号、开始时间、结束时间，和消费金额，并按消费金额降序排列
select C.id 显示卡号,userName 用户名,P.id 机器编号,beginTime 开始时间,endTime 结束时间,fee 消费金额 from tbl_record R 
inner join tbl_card C on C.id=cardId 
inner join tbl_computer P on P.id=R.ComputerId where userName='张军' order by fee DESC
--2. 查询出每台机器上的上网次数和消费的总金额 
select C.id 机器,count(ComputerId) 上网次数,sum(fee) 消费的总金额 from tbl_record R 
inner join tbl_computer C on C.id=R.ComputerId group by C.id

--3. 查询出所有已经使用过的上网卡的消费总金额
select cardId 上网卡,sum(fee) 消费总金额 from tbl_record group by cardId


--4. 查询出从未消费过的上网卡的卡号和用户名
select id,userName from tbl_card where id not in(select distinct cardId from tbl_record)


--5. 将密码与用户名一样的上网卡信息查询出来
select * from tbl_card where id=password
--6. 查询出使用次数最多的机器号和使用次数
select top 1 C.id,count(ComputerId) from tbl_record R inner join tbl_computer C on C.id=ComputerId group by C.id order by count(ComputerId) DESC
--7. 查询出卡号是以'ABC'结尾的卡号，用户名，上网的机器号和消费金额
select userName,P.id,fee from tbl_record R inner join tbl_computer P on ComputerId=P.id inner join tbl_card C on C.id=cardId where cardId like '%ABC'

--8. 查询出是周六、周天上网的记录，要求显示上网的卡号，用户名，机器号，开始时间、结束时间和消费金额
select cardId,userName,P.id,beginTime,endTime,fee from tbl_record inner join tbl_computer P on ComputerId=P.id inner join tbl_card C on C.id=cardId where datepart(weekday,beginTime)=1 or datepart(weekday,beginTime)=7

--9. 查询成一次上网时间超过12小时的的上网记录，要求显示上网的卡号，用户名，机器号，开始时间、结束时间和消费金额
select cardId,userName,P.id,beginTime,endTime,fee from tbl_record inner join tbl_computer P on ComputerId=P.id inner join tbl_card C on C.id=cardId where datediff(hour,beginTime,endTime)>12

--10. 查询除消费金额排列前三名(最高)的上网记录，要求显示上网的卡号，用户名，机器号，开始时间、结束时间和消费金额
select cardId,userName,P.id,beginTime,endTime,fee from tbl_record inner join tbl_computer P on ComputerId=P.id inner join tbl_card C on C.id=cardId where fee not in(select top 3 fee from tbl_record order by fee DESC)
